package com.hex.ds.hdtp.core.inf.metadata.service.impl;

import cn.hutool.core.util.StrUtil;
import com.hex.ds.hdtp.core.inf.metadata.converter.ITdSqlResultMapper;
import com.hex.ds.hdtp.core.inf.metadata.entity.TableFieldsEntity;
import com.hex.ds.hdtp.core.inf.metadata.repository.ISourceConfInfoRepository;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @Package com.hex.ds.hdtp.core.inf.metadata.service.impl
 * @ClassName TdMethodService
 * @Description Teradata元数据管理服务类
 * @Author gj.xu
 * @Date 2024/3/4 18:18
 * @Version v2.0
 **/
@Service("TD")
public class TdMethodService extends AbstractSourceMethodService {

    private final ITdSqlResultMapper tdSqlResultMapper;

    public TdMethodService(ISourceConfInfoRepository sourceConfInfoRepository, ITdSqlResultMapper tdSqlResultMapper) {
        super(sourceConfInfoRepository);
        this.tdSqlResultMapper = tdSqlResultMapper;
    }

    /**
     * @Method getSchemasSql
     * @Param
     * @Return java.lang.String
     * @Description 获取数据库Schemas的SQL
     * @Author gj.xu
     * @Date 2024/2/2 15:40
     * @Version V2.0
     */
    @Override
    public String getSchemasSql() {
        return "SELECT databasename FROM dbc.databasesV ORDER BY databasename";
    }

    /**
     * @Method getTablesSql
     * @Param schemaName
     * @Return java.lang.String
     * @Description 获取数据库Tables的SQL
     * @Author gj.xu
     * @Date 2024/2/2 15:44
     * @Version V2.0
     */
    @Override
    public String getTablesSql(String schemaName) {
        return StrUtil.format("SELECT tablename FROM dbc.tablesV WHERE databasename = '{}' ORDER BY tablename;", schemaName);
    }

    @Override
    protected String queryProceduresContentSql(String schemaName, String proceduresName) {
        throw new RuntimeException("Teradata 暂未支持获取存储过程");
    }

    @Override
    protected String getProcedureContentFromResultSet(ResultSet resultSet) throws SQLException {
        throw new RuntimeException("Teradata 暂未支持获取存储过程");
    }

    @Override
    protected TableFieldsEntity toTableFieldsBean(Map<String, String> tableFieldsMap) {
        return tdSqlResultMapper.toTableFieldsBean(tableFieldsMap);
    }

    /**
     * TD字段类型映射
     */
    private static final String COLUMN_TYPE_REF =
                "    CASE\n" +
                "        WHEN ColumnType = 'CF' THEN 'CHAR'\n" +
                "        WHEN ColumnType = 'BF' THEN 'BYTE'\n" +
                "        WHEN ColumnType = 'I' THEN 'INTEGER'\n" +
                "        WHEN ColumnType = 'I1' THEN 'BYTEINT'\n" +
                "        WHEN ColumnType = 'I2' THEN 'SMALLINT'\n" +
                "        WHEN ColumnType = 'I8' THEN 'BIGINT'\n" +
                "        WHEN ColumnType = 'TS' THEN 'TIMESTAMP'\n" +
                "        WHEN ColumnType = 'AT' THEN 'TIME'\n" +
                "        WHEN ColumnType = 'CV' THEN 'VARCHAR'\n" +
                "        WHEN ColumnType = 'BV' THEN 'VARBYTE'\n" +
                "        WHEN ColumnType = 'D'  THEN 'DECIMAL'\n" +
                "        WHEN ColumnType = 'DA' THEN 'DATE'\n" +
                "        WHEN ColumnType = 'F'  THEN 'FLOAT'\n" +
                "        WHEN ColumnType = 'N'  THEN 'NUMBER'\n" +
                "        -- 添加其他映射规则\n" +
                "        ELSE ColumnType  -- 如果没有映射规则，保持原始类型\n" +
                "    END ";

    @Override
    protected String queryTableStructureSql(String schemaName, String tableName) {
        String querySql = "SELECT\n" +
                "\tColumnId AS ColumnId,\n" +
                "    DatabaseName AS DataBaseName,\n" +
                "    TableName AS TableName,\n" +
                "     ColumnName AS ColumnName,\n" +
                COLUMN_TYPE_REF + "AS ColumnType,\n" +
                "    (CASE WHEN CharType = '0' \n" +
                "    \tTHEN \n" +
                "            (CASE WHEN DecimalFractionalDigits IS NOT NULL \n" +
                "            \tTHEN TRIM(DecimalTotalDigits || ',' || TRIM(DecimalFractionalDigits)) \n" +
                "                ELSE TRIM(COALESCE(DecimalTotalDigits, 0))\n" +
                "            END)\n" +
                "        ELSE TRIM(ColumnLength)\n" +
                "    END\n" +
                "   )\n" +
                "     AS columnLength,\n" +
                "    CASE\n" +
                "        WHEN ColumnType = 'CF' THEN 'CHAR('|| TRIM(ColumnLength) ||')'\n" +
                "        WHEN ColumnType = 'BF' THEN 'BYTE('|| TRIM(ColumnLength) ||')'\n" +
                "        WHEN ColumnType = 'I' THEN 'INTEGER'\n" +
                "        WHEN ColumnType = 'I1' THEN 'BYTEINT'\n" +
                "        WHEN ColumnType = 'I2' THEN 'SMALLINT'\n" +
                "        WHEN ColumnType = 'I8' THEN 'BIGINT'\n" +
                "        WHEN ColumnType = 'TS' THEN 'TIMESTAMP'\n" +
                "        WHEN ColumnType = 'AT' THEN 'TIME'\n" +
                "        WHEN ColumnType = 'CV' THEN 'VARCHAR('|| TRIM(ColumnLength) ||')'\n" +
                "        WHEN ColumnType = 'BV' THEN 'VARCHAR('|| TRIM(ColumnLength) ||')'\n" +
                "        WHEN ColumnType = 'D'  THEN 'DECIMAL('|| TRIM(DecimalTotalDigits || ',' ||TRIM(DecimalFractionalDigits)) ||')'\n" +
                "        WHEN ColumnType = 'DA' THEN 'DATE'\n" +
                "        WHEN ColumnType = 'F'  THEN 'FLOAT('|| TRIM(DecimalTotalDigits || ',' ||TRIM(DecimalFractionalDigits)) ||')'\n" +
                "        WHEN ColumnType = 'N'  THEN 'NUMBER('|| TRIM(DecimalTotalDigits || ',' ||TRIM(DecimalFractionalDigits)) ||')'\n" +
                "        -- 添加其他映射规则\n" +
                "        ELSE ColumnType  -- 如果没有映射规则，保持原始类型\n" +
                "    END AS columnTypeLength,\n" +
                "    ColumnTitle AS ColumnCommet,Nullable AS isAllowNull,PartitioningColumn AS PartitioningColumn\n" +
                "FROM dbc.columnsV\n" +
                "WHERE databasename = '{}' \n" +
                "  AND tablename = '{}'\n" +
                " order by ColumnId";
        return StrUtil.format(querySql, schemaName, tableName);
    }


    /**
     * @Method getTableAllFieldSqlLimitSql
     * @Param fields
     * @Param schema
     * @Param table
     * @Param whereSql
     * @Param extractScope
     * @Param offset
     * @Param sortField
     * @Return java.lang.String
     * @Description 获取分批比对全字段明细对比的SQL
     * @Author gj.xu
     * @Date 2024/2/1 20:53
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldSqlLimitSql(String fields, String schema, String table, String whereSql, Long extractScope, Long offset, String sortField) {
        String querySql = "SELECT {}\n" +
                "FROM (\n" +
                "  SELECT {}, ROW_NUMBER() OVER (ORDER BY {}) AS rnum\n" +
                "  FROM {}.{} {}\n" +
                ")AS PagedResults\n" +
                "WHERE rnum BETWEEN {} AND {}\n" +
                "ORDER BY rnum";
        return StrUtil.format(querySql, fields, fields, sortField, schema, table, whereSql, offset + 1, offset + extractScope);
    }


    /**
     * @Method getTableShowCreateTableSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取查询表建表语句的SQL
     * @Author gj.xu
     * @Date 2024/1/31 17:18
     * @Version V2.0
     */
    @Override
    public String getTableShowCreateTableSql(String schema, String table) {
        String querySql = "SHOW TABLE {}.{}";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method getTableAllFieldNumSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取字段总数SQL
     * @Author gj.xu
     * @Date 2024/2/1 11:12
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldNumSql(String schema, String table) {
        String querySql = "SELECT COUNT(1) FROM dbc.columnsV WHERE databasename = '{}' AND tablename = '{}'";
        return StrUtil.format(querySql, schema, table);
    }

    /**
     * @Method getTableAllFieldNameSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取所有字段名称SQL
     * @Author gj.xu
     * @Date 2024/2/1 11:44
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldNameSql(String schema, String table) {
        String querySql = "SELECT ColumnName FROM DBC.Columns WHERE DatabaseName = '{}' AND TableName = '{}';";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method getTableAllFieldLengthSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取所有字段长度SQL
     * @Author gj.xu
     * @Date 2024/2/1 14:20
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldLengthSql(String schema, String table) {
        String querySql = "SELECT ColumnName as column_name, ColumnLength as data_length FROM dbc.columnsV WHERE databasename = '{}' AND tablename = '{}'";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method getTableFieldAccuracySql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取字段精度SQL
     * @Author gj.xu
     * @Date 2024/2/1 15:20
     * @Version V2.0
     */
    @Override
    public String getTableFieldAccuracySql(String schema, String table) {
        String querySql = "SELECT ColumnName AS column_name ,TRIM(COALESCE(DecimalFractionalDigits, '0')) as column_accuracy FROM dbc.columnsV WHERE databasename = '{}' AND tablename = '{}' order by ColumnId";
        return StrUtil.format(querySql, schema, table);
    }

    /**
     * @Method getTableAllFieldTypeSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取字段类型SQL
     * @Author gj.xu
     * @Date 2024/2/1 15:45
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldTypeSql(String schema, String table) {
        String querySql = " SELECT ColumnName as column_name,\n" +
                COLUMN_TYPE_REF + "AS data_type\n" +
                " FROM dbc.columnsV\n" +
                "WHERE databasename = '{}' \n" +
                "  AND tablename = '{}'\n" +
                " order by ColumnId";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method queryTableExistSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取表是否存在SQL
     * @Author gj.xu
     * @Date 2024/2/1 17:02
     * @Version V2.0
     */
    public String queryTableExistSql(String schema, String table) {
        String querySql = " SELECT TOP 1 TableName FROM DBC.TablesV WHERE DatabaseName = '{}' AND TableName = '{}';";
        return StrUtil.format(querySql, schema, table);
    }

    /**
     * @Method getTablePartitionFieldSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取分区字段的SQL
     * @Author gj.xu
     * @Date 2024/2/1 20:20
     * @Version V2.0
     */
    @Override
    public String getTablePartitionFieldSql(String schema, String table) {
        String querySql = "SELECT ColumnName FROM DBC.columnsV WHERE databasename = '{}' AND tablename = '{}' AND PartitioningColumn = 'Y';";
        return StrUtil.format(querySql, schema, table);
    }

    /**
     * @Method getStrDataType
     * @Param
     * @Return java.util.List<java.lang.String>
     * @Description 获取字符数据类型
     * @Author gj.xu
     * @Date 2024/2/21 14:20
     * @Version V2.0
     */
    @Override
    public List<String> getStrDataType() {
        return Arrays.asList("CHARACTER", "VARCHAR", "CHAR", "CLOB", "JSON");
    }

    /**
     * @Method getNumDataType
     * @Param
     * @Return java.util.List<java.lang.String>
     * @Description 获取数值数据类型
     * @Author gj.xu
     * @Date 2024/2/21 15:10
     * @Version V2.0
     */
    @Override
    public List<String> getNumDataType() {
        return Arrays.asList("NUMBER", "FLOAT", "DOUBLE PRECISION", "DECIMAL", "INTEGER", "SMALLINT", "BIGINT");
    }

    /**
     * @Method getAccuracyDataType
     * @Param
     * @Return java.util.List<java.lang.String>
     * @Description 获取精度数据类型
     * @Author gj.xu
     * @Date 2024/2/21 15:32
     * @Version V2.0
     */
    @Override
    public List<String> getAccuracyDataType() {
        return Arrays.asList("DECIMAL");
    }
}
